{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "602f3628",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "#import glob\n",
    "import tqdm\n",
    "import random\n",
    "import numpy as np\n",
    "from sklearn.model_selection import train_test_split\n",
    "import time,datetime\n",
    "import variable_bin_methods as varbin_meth\n",
    "import variable_encode as var_encode\n",
    "from sklearn.model_selection import GridSearchCV\n",
    "from sklearn.metrics import roc_curve, auc,confusion_matrix,recall_score,precision_score,accuracy_score\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from imblearn.under_sampling import RandomUnderSampler\n",
    "from imblearn.over_sampling import SMOTE\n",
    "from imblearn.over_sampling import BorderlineSMOTE\n",
    "import missingno as msno\n",
    "import matplotlib\n",
    "#matplotlib.use(arg='Qt5Agg')\n",
    "import matplotlib.pyplot as plt\n",
    "matplotlib.rcParams['font.sans-serif']=['SimHei']   \n",
    "matplotlib.rcParams['axes.unicode_minus']=False  \n",
    "from statsmodels.stats.outliers_influence import variance_inflation_factor\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\") ##忽略警告\n",
    "import variable_bin_methods as vbm\n",
    "import pickle\n",
    "import copy\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0878f16",
   "metadata": {},
   "source": [
    "# 读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c52314f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1 = pd.read_csv('2019年全部原始数据.csv',header = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4074d54d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(518107, 150)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1449f8d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "## 将空值填充为 '999999999'\n",
    "df_1['bc_open_to_buy'] = df_1['bc_open_to_buy'].fillna('999999999.0')\n",
    "## 将空值填充为 '999999999'\n",
    "df_1['bc_util'] = df_1['bc_util'].fillna('999999999.0')\n",
    "## 将空值填充为 '999999999'\n",
    "df_1['mo_sin_old_il_acct'] = df_1['mo_sin_old_il_acct'].fillna('999999999.0')\n",
    "## 将空值填充为 '999999999'\n",
    "df_1['dti'] = df_1['dti'].fillna('999999999.0')\n",
    "## 将空值填充为 '999999999'\n",
    "df_1['mths_since_recent_inq'] = df_1['mths_since_recent_inq'].fillna('999999999.0')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c9fd7313",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1.to_excel('2019调整后的数据(程序内部使用).xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c90cefe3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_10 = pd.read_excel('2019调整后的数据(程序内部使用).xlsx',header = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b7e566b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_10['bc_open_to_buy'] = df_10['bc_open_to_buy']+1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5d5ffa20",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_10['bc_util'] = df_10['bc_util']+1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0972cb8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_10['mo_sin_old_il_acct'] = df_10['mo_sin_old_il_acct']+1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a8c1eda2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_10['dti'] = df_10['dti']+1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e221a820",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_10['mths_since_recent_inq'] = df_10['mths_since_recent_inq']+1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "1f843308",
   "metadata": {},
   "outputs": [],
   "source": [
    "## 将 '1000000000'还原为0\n",
    "df_10['bc_open_to_buy'] = df_10['bc_open_to_buy'].replace([1000000000],[0])\n",
    "## 将 '1000000000'还原为0\n",
    "df_10['bc_util'] = df_10['bc_util'].replace([1000000000],[0])\n",
    "## 将 '1000000000'还原为0\n",
    "df_10['mo_sin_old_il_acct'] = df_10['mo_sin_old_il_acct'].replace([1000000000],[0])\n",
    "## 将 '1000000000'还原为0\n",
    "df_10['dti'] = df_10['dti'].replace([1000000000],[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "23890f20",
   "metadata": {},
   "outputs": [],
   "source": [
    "## 根据Bivar图进行调整\n",
    "df_10['bc_open_to_buy'] = df_10['bc_open_to_buy'].replace([0],[12000])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7aea6c9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "## 根据Bivar图进行调整\n",
    "df_10['bc_util'] = df_10['bc_util'].replace([0],[40])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8d17ebdf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>member_id</th>\n",
       "      <th>loan_amnt</th>\n",
       "      <th>funded_amnt</th>\n",
       "      <th>funded_amnt_inv</th>\n",
       "      <th>term</th>\n",
       "      <th>int_rate</th>\n",
       "      <th>installment</th>\n",
       "      <th>grade</th>\n",
       "      <th>sub_grade</th>\n",
       "      <th>...</th>\n",
       "      <th>orig_projected_additional_accrued_interest</th>\n",
       "      <th>hardship_payoff_balance_amount</th>\n",
       "      <th>hardship_last_payment_amount</th>\n",
       "      <th>debt_settlement_flag</th>\n",
       "      <th>debt_settlement_flag_date</th>\n",
       "      <th>settlement_status</th>\n",
       "      <th>settlement_date</th>\n",
       "      <th>settlement_amount</th>\n",
       "      <th>settlement_percentage</th>\n",
       "      <th>settlement_term</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>148763841</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5000</td>\n",
       "      <td>5000</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>13.90%</td>\n",
       "      <td>170.65</td>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>149089105</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6500</td>\n",
       "      <td>6500</td>\n",
       "      <td>6475.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>8.81%</td>\n",
       "      <td>206.13</td>\n",
       "      <td>A</td>\n",
       "      <td>A5</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>149203043</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24000</td>\n",
       "      <td>24000</td>\n",
       "      <td>24000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>13.90%</td>\n",
       "      <td>557.20</td>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>...</td>\n",
       "      <td>473.24</td>\n",
       "      <td>20656.42</td>\n",
       "      <td>557.2</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>149354242</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18500</td>\n",
       "      <td>18500</td>\n",
       "      <td>18500.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>14.74%</td>\n",
       "      <td>437.60</td>\n",
       "      <td>C</td>\n",
       "      <td>C2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>149461986</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24000</td>\n",
       "      <td>24000</td>\n",
       "      <td>24000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>20.00%</td>\n",
       "      <td>635.86</td>\n",
       "      <td>D</td>\n",
       "      <td>D2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518102</th>\n",
       "      <td>158872331</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3000</td>\n",
       "      <td>3000</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>17.74%</td>\n",
       "      <td>108.07</td>\n",
       "      <td>C</td>\n",
       "      <td>C5</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518103</th>\n",
       "      <td>158833440</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10000</td>\n",
       "      <td>10000</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>6.46%</td>\n",
       "      <td>306.31</td>\n",
       "      <td>A</td>\n",
       "      <td>A1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518104</th>\n",
       "      <td>158748525</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19000</td>\n",
       "      <td>19000</td>\n",
       "      <td>19000.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>6.46%</td>\n",
       "      <td>581.99</td>\n",
       "      <td>A</td>\n",
       "      <td>A1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518105</th>\n",
       "      <td>158298751</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10000</td>\n",
       "      <td>10000</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>60 months</td>\n",
       "      <td>28.80%</td>\n",
       "      <td>316.21</td>\n",
       "      <td>D</td>\n",
       "      <td>D5</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518106</th>\n",
       "      <td>158206429</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14875</td>\n",
       "      <td>14875</td>\n",
       "      <td>14875.0</td>\n",
       "      <td>36 months</td>\n",
       "      <td>16.95%</td>\n",
       "      <td>529.97</td>\n",
       "      <td>C</td>\n",
       "      <td>C4</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>518107 rows × 150 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               id  member_id  loan_amnt  funded_amnt  funded_amnt_inv  \\\n",
       "0       148763841        NaN       5000         5000           5000.0   \n",
       "1       149089105        NaN       6500         6500           6475.0   \n",
       "2       149203043        NaN      24000        24000          24000.0   \n",
       "3       149354242        NaN      18500        18500          18500.0   \n",
       "4       149461986        NaN      24000        24000          24000.0   \n",
       "...           ...        ...        ...          ...              ...   \n",
       "518102  158872331        NaN       3000         3000           3000.0   \n",
       "518103  158833440        NaN      10000        10000          10000.0   \n",
       "518104  158748525        NaN      19000        19000          19000.0   \n",
       "518105  158298751        NaN      10000        10000          10000.0   \n",
       "518106  158206429        NaN      14875        14875          14875.0   \n",
       "\n",
       "              term int_rate  installment grade sub_grade  ...  \\\n",
       "0        36 months   13.90%       170.65     C        C1  ...   \n",
       "1        36 months    8.81%       206.13     A        A5  ...   \n",
       "2        60 months   13.90%       557.20     C        C1  ...   \n",
       "3        60 months   14.74%       437.60     C        C2  ...   \n",
       "4        60 months   20.00%       635.86     D        D2  ...   \n",
       "...            ...      ...          ...   ...       ...  ...   \n",
       "518102   36 months   17.74%       108.07     C        C5  ...   \n",
       "518103   36 months    6.46%       306.31     A        A1  ...   \n",
       "518104   36 months    6.46%       581.99     A        A1  ...   \n",
       "518105   60 months   28.80%       316.21     D        D5  ...   \n",
       "518106   36 months   16.95%       529.97     C        C4  ...   \n",
       "\n",
       "       orig_projected_additional_accrued_interest  \\\n",
       "0                                             NaN   \n",
       "1                                             NaN   \n",
       "2                                          473.24   \n",
       "3                                             NaN   \n",
       "4                                             NaN   \n",
       "...                                           ...   \n",
       "518102                                        NaN   \n",
       "518103                                        NaN   \n",
       "518104                                        NaN   \n",
       "518105                                        NaN   \n",
       "518106                                        NaN   \n",
       "\n",
       "       hardship_payoff_balance_amount hardship_last_payment_amount  \\\n",
       "0                                 NaN                          NaN   \n",
       "1                                 NaN                          NaN   \n",
       "2                            20656.42                        557.2   \n",
       "3                                 NaN                          NaN   \n",
       "4                                 NaN                          NaN   \n",
       "...                               ...                          ...   \n",
       "518102                            NaN                          NaN   \n",
       "518103                            NaN                          NaN   \n",
       "518104                            NaN                          NaN   \n",
       "518105                            NaN                          NaN   \n",
       "518106                            NaN                          NaN   \n",
       "\n",
       "        debt_settlement_flag debt_settlement_flag_date settlement_status  \\\n",
       "0                          N                       NaN               NaN   \n",
       "1                          N                       NaN               NaN   \n",
       "2                          N                       NaN               NaN   \n",
       "3                          N                       NaN               NaN   \n",
       "4                          N                       NaN               NaN   \n",
       "...                      ...                       ...               ...   \n",
       "518102                     N                       NaN               NaN   \n",
       "518103                     N                       NaN               NaN   \n",
       "518104                     N                       NaN               NaN   \n",
       "518105                     N                       NaN               NaN   \n",
       "518106                     N                       NaN               NaN   \n",
       "\n",
       "       settlement_date settlement_amount settlement_percentage  \\\n",
       "0                  NaN               NaN                   NaN   \n",
       "1                  NaN               NaN                   NaN   \n",
       "2                  NaN               NaN                   NaN   \n",
       "3                  NaN               NaN                   NaN   \n",
       "4                  NaN               NaN                   NaN   \n",
       "...                ...               ...                   ...   \n",
       "518102             NaN               NaN                   NaN   \n",
       "518103             NaN               NaN                   NaN   \n",
       "518104             NaN               NaN                   NaN   \n",
       "518105             NaN               NaN                   NaN   \n",
       "518106             NaN               NaN                   NaN   \n",
       "\n",
       "        settlement_term  \n",
       "0                   NaN  \n",
       "1                   NaN  \n",
       "2                   NaN  \n",
       "3                   NaN  \n",
       "4                   NaN  \n",
       "...                 ...  \n",
       "518102              NaN  \n",
       "518103              NaN  \n",
       "518104              NaN  \n",
       "518105              NaN  \n",
       "518106              NaN  \n",
       "\n",
       "[518107 rows x 150 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "eefb7a2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_X = df_10.drop(columns = ['loan_status'])\n",
    "data_y = df_10['loan_status']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd38095d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "4d967444",
   "metadata": {},
   "outputs": [],
   "source": [
    "##不放回随机抽取四份样本\n",
    "index_list = list(np.arange(0,518107))\n",
    "num = int(len(index_list))\n",
    "index_random = random.sample(index_list,num)\n",
    "my_index = []\n",
    "for i in np.arange(0,len(index_random),int(len(index_random) / 4)):\n",
    "    my_index.append(index_random[i:(i + int(len(index_random) / 4))])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "8bb39417",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_11=df_10.loc[my_index[0]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "b9e25959",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_12=df_10.loc[my_index[1]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "9d9d9bfe",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_13=df_10.loc[my_index[2]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "3051d362",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_14=df_10.loc[my_index[3]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "c2bc3f12",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((129526, 150), (129526, 150))"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_11.shape,df_12.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "8c20fff9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((129526, 150), (129526, 150))"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_13.shape,df_14.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "62a1d669",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_X_1 = df_11.drop(columns = ['loan_status'])\n",
    "data_y_1 = df_11['loan_status']\n",
    "data_X_2= df_12.drop(columns = ['loan_status'])\n",
    "data_y_2 = df_12['loan_status']\n",
    "data_X_3 = df_13.drop(columns = ['loan_status'])\n",
    "data_y_3 = df_13['loan_status']\n",
    "data_X_4 = df_14.drop(columns = ['loan_status'])\n",
    "data_y_4 = df_14['loan_status']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "4d12c5cf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3195      Fully Paid\n",
       "434656       Current\n",
       "81024     Fully Paid\n",
       "223966    Fully Paid\n",
       "411126       Current\n",
       "             ...    \n",
       "360741    Fully Paid\n",
       "472924       Current\n",
       "67487        Current\n",
       "40353        Current\n",
       "484970       Current\n",
       "Name: loan_status, Length: 129526, dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_y_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "3e9a59ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_train = pd.read_excel('最终2018年改变分箱最后19个特征数据的分箱.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "10340eb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "## 读取上一步存的分箱规则和woe规则\n",
    "continuous_var_bin_read = open('continuous_var_bin.pkl','rb')\n",
    "continuous_var_bin_dict = pickle.load(continuous_var_bin_read)\n",
    "continuous_var_bin_read.close()\n",
    "\n",
    "categorical_var_bin_read = open('categorical_var_bin.pkl','rb')\n",
    "categorical_var_bin_dict = pickle.load(categorical_var_bin_read)\n",
    "categorical_var_bin_read.close()\n",
    "\n",
    "woe_list_read = open('woe_list.pkl','rb')\n",
    "woe_list_dict = pickle.load(woe_list_read)\n",
    "woe_list_read.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "b24c33cc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>term</th>\n",
       "      <th>fico_range_high</th>\n",
       "      <th>installment</th>\n",
       "      <th>tot_hi_cred_lim</th>\n",
       "      <th>mort_acc</th>\n",
       "      <th>bc_open_to_buy</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>open_rv_24m</th>\n",
       "      <th>bc_util</th>\n",
       "      <th>num_actv_rev_tl</th>\n",
       "      <th>mo_sin_old_rev_tl_op</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>num_il_tl</th>\n",
       "      <th>mo_sin_old_il_acct</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>dti</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>mths_since_recent_inq</th>\n",
       "      <th>loan_status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260144</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260145</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260146</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260147</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260148</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>260149 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        term  fico_range_high  installment  tot_hi_cred_lim  mort_acc  \\\n",
       "0          1                0            3                2         2   \n",
       "1          0                2            3                2         3   \n",
       "2          0                1            1                1         0   \n",
       "3          0                2            1                2         0   \n",
       "4          0                3            2                4         1   \n",
       "...      ...              ...          ...              ...       ...   \n",
       "260144     1                0            3                2         1   \n",
       "260145     0                1            1                0         0   \n",
       "260146     0                1            3                1         2   \n",
       "260147     0                0            1                2         0   \n",
       "260148     0                1            4                3         3   \n",
       "\n",
       "        bc_open_to_buy  home_ownership  verification_status  open_rv_24m  \\\n",
       "0                    3               1                    1            3   \n",
       "1                    3               1                    2            0   \n",
       "2                    0               2                    0            0   \n",
       "3                    3               0                    1            1   \n",
       "4                    1               0                    1            1   \n",
       "...                ...             ...                  ...          ...   \n",
       "260144               0               0                    0            1   \n",
       "260145               0               1                    1            3   \n",
       "260146               0               1                    0            0   \n",
       "260147               0               0                    1            3   \n",
       "260148               4               0                    1            3   \n",
       "\n",
       "        bc_util  num_actv_rev_tl  mo_sin_old_rev_tl_op  emp_length  num_il_tl  \\\n",
       "0             1                2                     2           4          1   \n",
       "1             5                0                     4           2          4   \n",
       "2             5                2                     4           4          3   \n",
       "3             1                0                     4           4          4   \n",
       "4             1                2                     4           3          4   \n",
       "...         ...              ...                   ...         ...        ...   \n",
       "260144        3                4                     4           4          1   \n",
       "260145        3                0                     0           0          0   \n",
       "260146        5                2                     4           4          1   \n",
       "260147        5                1                     4           2          1   \n",
       "260148        3                1                     4           4          1   \n",
       "\n",
       "        mo_sin_old_il_acct  annual_inc  dti  inq_last_6mths  \\\n",
       "0                        2           2    2               0   \n",
       "1                        5           2    2               0   \n",
       "2                        5           4    3               0   \n",
       "3                        4           2    3               0   \n",
       "4                        5           4    2               0   \n",
       "...                    ...         ...  ...             ...   \n",
       "260144                   5           2    1               1   \n",
       "260145                   2           0    2               1   \n",
       "260146                   3           1    2               0   \n",
       "260147                   4           1    2               0   \n",
       "260148                   1           2    2               2   \n",
       "\n",
       "        mths_since_recent_inq  loan_status  \n",
       "0                           0            0  \n",
       "1                           4            0  \n",
       "2                           4            0  \n",
       "3                           1            0  \n",
       "4                           4            0  \n",
       "...                       ...          ...  \n",
       "260144                      1            0  \n",
       "260145                      2            0  \n",
       "260146                      4            0  \n",
       "260147                      4            0  \n",
       "260148                      1            1  \n",
       "\n",
       "[260149 rows x 20 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_train"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c49168e",
   "metadata": {},
   "source": [
    "# 处理异常特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "19de0990",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_X = data_X[data_train.drop(columns = ['loan_status']).columns]\n",
    "data_X_1 = data_X_1[data_train.drop(columns = ['loan_status']).columns]\n",
    "data_X_2 = data_X_2[data_train.drop(columns = ['loan_status']).columns]\n",
    "data_X_3 = data_X_3[data_train.drop(columns = ['loan_status']).columns]\n",
    "data_X_4 = data_X_4[data_train.drop(columns = ['loan_status']).columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "189a9897",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['term', 'fico_range_high', 'installment', 'tot_hi_cred_lim', 'mort_acc',\n",
       "       'bc_open_to_buy', 'home_ownership', 'verification_status',\n",
       "       'open_rv_24m', 'bc_util', 'num_actv_rev_tl', 'mo_sin_old_rev_tl_op',\n",
       "       'emp_length', 'num_il_tl', 'mo_sin_old_il_acct', 'annual_inc', 'dti',\n",
       "       'inq_last_6mths', 'mths_since_recent_inq'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_X_1.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "eb020db0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#data_X['revol_util']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "a0dcd38c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#data_X['revol_util'].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "7381b25e",
   "metadata": {},
   "outputs": [],
   "source": [
    "## 将空值填充为 '999999999'\n",
    "#data_X['revol_util'] = data_X['revol_util'].fillna('999999999')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d97e0499",
   "metadata": {},
   "outputs": [],
   "source": [
    "#data_X['revol_util'] = data_X['revol_util'].apply(lambda x: x.strip())\n",
    "#data_X['revol_util'] = data_X['revol_util'].apply(lambda x: float(x[:-1]) / 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "9b37427f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#data_X['revol_util'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "ba6fd417",
   "metadata": {},
   "outputs": [],
   "source": [
    "## 把本来缺失的值还原为空\n",
    "#data_X['revol_util'] = data_X['revol_util'].replace([999999.99],np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "7696621b",
   "metadata": {},
   "outputs": [],
   "source": [
    "## 检查是否把空还原\n",
    "#data_X['revol_util'].isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43dda660",
   "metadata": {},
   "source": [
    "# 定义标签"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "a621bf0e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Fully Paid',\n",
       " 'Current',\n",
       " 'Charged Off',\n",
       " 'In Grace Period',\n",
       " 'Late (31-120 days)',\n",
       " 'Late (16-30 days)',\n",
       " 'Default']"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(data_y_1.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "81504db8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Current',\n",
       " 'Fully Paid',\n",
       " 'Charged Off',\n",
       " 'In Grace Period',\n",
       " 'Default',\n",
       " 'Late (31-120 days)',\n",
       " 'Late (16-30 days)']"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(data_y_2.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "bed6cde4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Charged Off',\n",
       " 'Fully Paid',\n",
       " 'Current',\n",
       " 'Late (31-120 days)',\n",
       " 'In Grace Period',\n",
       " 'Late (16-30 days)',\n",
       " 'Default']"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(data_y_3.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "2d6e74c2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Current',\n",
       " 'Fully Paid',\n",
       " 'Charged Off',\n",
       " 'In Grace Period',\n",
       " 'Late (31-120 days)',\n",
       " 'Late (16-30 days)',\n",
       " 'Default']"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(data_y_4.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "371ed0c6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Current               95907\n",
       "Fully Paid            24217\n",
       "Charged Off            5449\n",
       "Late (31-120 days)     1677\n",
       "In Grace Period        1566\n",
       "Late (16-30 days)       441\n",
       "Default                 269\n",
       "Name: loan_status, dtype: int64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_y_1.value_counts(dropna = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "0b7ea903",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Current               95931\n",
       "Fully Paid            24125\n",
       "Charged Off            5487\n",
       "Late (31-120 days)     1750\n",
       "In Grace Period        1563\n",
       "Late (16-30 days)       405\n",
       "Default                 265\n",
       "Name: loan_status, dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_y_2.value_counts(dropna = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "afaf21b1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Current               95734\n",
       "Fully Paid            24548\n",
       "Charged Off            5481\n",
       "Late (31-120 days)     1598\n",
       "In Grace Period        1482\n",
       "Late (16-30 days)       409\n",
       "Default                 274\n",
       "Name: loan_status, dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_y_3.value_counts(dropna = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "ce318613",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Current               95985\n",
       "Fully Paid            24158\n",
       "Charged Off            5519\n",
       "Late (31-120 days)     1663\n",
       "In Grace Period        1502\n",
       "Late (16-30 days)       455\n",
       "Default                 244\n",
       "Name: loan_status, dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_y_4.value_counts(dropna = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "f9973384",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_y = data_y.replace(['Current','Fully Paid','Default','In Grace Period','Late (16-30 days)',\n",
    "                                                     'Late (31-120 days)','Charged Off'],\n",
    "                                                    [2,0,1,1,1,1,1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "7f11a8e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_y_1 = data_y_1.replace(['Current','Fully Paid','Default','In Grace Period','Late (16-30 days)',\n",
    "                                                     'Late (31-120 days)','Charged Off'],\n",
    "                                                    [2,0,1,1,1,1,1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "a9da1b40",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_y_2 = data_y_2.replace(['Current','Fully Paid','Default','In Grace Period','Late (16-30 days)',\n",
    "                                                     'Late (31-120 days)','Charged Off'],\n",
    "                                                    [2,0,1,1,1,1,1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "34ca95d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_y_3 = data_y_3.replace(['Current','Fully Paid','Default','In Grace Period','Late (16-30 days)',\n",
    "                                                     'Late (31-120 days)','Charged Off'],\n",
    "                                                    [2,0,1,1,1,1,1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "56c2d30d",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_y_4 = data_y_4.replace(['Current','Fully Paid','Default','In Grace Period','Late (16-30 days)',\n",
    "                                                     'Late (31-120 days)','Charged Off'],\n",
    "                                                    [2,0,1,1,1,1,1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "59bafafd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    383559\n",
       "0     97048\n",
       "1     37500\n",
       "Name: loan_status, dtype: int64"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_y.value_counts(dropna = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "bbcabdc1",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    95907\n",
       "0    24217\n",
       "1     9402\n",
       "Name: loan_status, dtype: int64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_y_1.value_counts(dropna = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "2c208d32",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    95931\n",
       "0    24125\n",
       "1     9470\n",
       "Name: loan_status, dtype: int64"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_y_2.value_counts(dropna = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "f2ccda2d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    95734\n",
       "0    24548\n",
       "1     9244\n",
       "Name: loan_status, dtype: int64"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_y_3.value_counts(dropna = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "b4b9573c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    95985\n",
       "0    24158\n",
       "1     9383\n",
       "Name: loan_status, dtype: int64"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_y_4.value_counts(dropna = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "c3005163",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_X = data_X[data_y != 2]\n",
    "data_X_1 = data_X_1[data_y_1 != 2]\n",
    "data_X_2 = data_X_2[data_y_2 != 2]\n",
    "data_X_3 = data_X_3[data_y_3 != 2]\n",
    "data_X_4 = data_X_4[data_y_4 != 2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "7a0774e8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((33619, 19), (33595, 19), (33792, 19), (33541, 19))"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_X_1.shape,data_X_2.shape,data_X_3.shape,data_X_4.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "f5dad8dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_y = data_y[data_y != 2]\n",
    "data_y_1 = data_y_1[data_y_1 != 2]\n",
    "data_y_2 = data_y_2[data_y_2 != 2]\n",
    "data_y_3 = data_y_3[data_y_3 != 2]\n",
    "data_y_4 = data_y_4[data_y_4 != 2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "618052cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([data_X_1,data_y_1],axis = 1).to_excel('最终2019年-1分箱调整所需要的数据.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "c75c8d5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([data_X_2,data_y_2],axis = 1).to_excel('最终2019年-2分箱调整所需要的数据.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "efa9029a",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([data_X_3,data_y_3],axis = 1).to_excel('最终2019年-3分箱调整所需要的数据.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "b691a894",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([data_X_4,data_y_4],axis = 1).to_excel('最终2019年-4分箱调整所需要的数据.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "0bdde82f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((33619,), (33595,), (33792,), (33541,))"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_y_1.shape,data_y_2.shape,data_y_3.shape,data_y_4.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "73ecb105",
   "metadata": {},
   "outputs": [],
   "source": [
    "分箱={'term':{0:[' 36 months'],1:[' 60 months']},'fico_range_high':[-np.inf,690,710,750,800,np.inf],\n",
    "   'installment':[-np.inf,160,330,500,700,np.inf],'tot_hi_cred_lim':[-np.inf,39000,120000,240000,500000,np.inf],\n",
    "   'mort_acc':[-np.inf,1,2,3,np.inf],'bc_open_to_buy':[-np.inf,6000,11999.99999,12000.00001,20000,40000,np.inf],\n",
    "   'home_ownership':{0:['MORTGAGE'],1:['OWN'],2:['RENT','ANY']},'verification_status':{0:['Not Verified'],1:['Source Verified'],2:['Verified']},\n",
    "   'open_rv_24m':[-np.inf,2,3,4,7,np.inf],'bc_util':[-np.inf,20,39.99999,40.00001,55,70,np.inf],\n",
    "   'num_actv_rev_tl':[-np.inf,4,5,7,9,np.inf],'mo_sin_old_rev_tl_op':[-np.inf,50,85,130,150,np.inf],\n",
    "   'emp_length':{0:['nan'],1:['< 1 year','1 year'],2:['2 years','3 years','4 years','5 years'],3:['6 years','7 years','8 years'],4:['9 years','10+ years']},\n",
    "   'num_il_tl':[-np.inf,2,4,7,11,np.inf],'mo_sin_old_il_acct':[-np.inf,0.00001,30,70,95,130,np.inf],\n",
    "   'annual_inc':[-np.inf,31000,50000,90000,100000,np.inf],'dti':[-np.inf,0.00001,15,25,35,45,np.inf],\n",
    "   'inq_last_6mths':[-np.inf,0.5,1.5,2.5,np.inf],'mths_since_recent_inq':[-np.inf,3,5,7,10,999999999.99999,np.inf]}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "e764da5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "空值分箱 = {'emp_length':0}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "99030069",
   "metadata": {},
   "outputs": [],
   "source": [
    "dis_cols = ['term','home_ownership','verification_status','emp_length']#,,'bc_open_to_buy''bc_open_to_buy','bc_util','mo_sin_old_il_acct','dti','mths_since_recent_inq'#]\n",
    "sample1_bins = copy.deepcopy(data_X_1)\n",
    "for k,v in 分箱.items():\n",
    "    x = sample1_bins[k]\n",
    "    bins = 分箱.get(k)\n",
    "    if k in dis_cols:\n",
    "        old_values,new_values = [],[]\n",
    "        for k_,v_ in bins.items():\n",
    "            old_values.extend(v_)\n",
    "            new_values.extend([k_] * len(v_))\n",
    "            sample1_bins[k] = x.replace(old_values + [np.nan],new_values + [空值分箱.get(k)])\n",
    "    else:\n",
    "        labels = list(np.arange(0,len(bins) - 1,dtype = int))\n",
    "        x = pd.cut(x,bins = bins,labels = labels)\n",
    "        sample1_bins[k] = x.replace([np.nan],[空值分箱.get(k)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "0cf69f11",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "sample1_bins_1=sample1_bins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "2b6a0e47",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>term</th>\n",
       "      <th>fico_range_high</th>\n",
       "      <th>installment</th>\n",
       "      <th>tot_hi_cred_lim</th>\n",
       "      <th>mort_acc</th>\n",
       "      <th>bc_open_to_buy</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>open_rv_24m</th>\n",
       "      <th>bc_util</th>\n",
       "      <th>num_actv_rev_tl</th>\n",
       "      <th>mo_sin_old_rev_tl_op</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>num_il_tl</th>\n",
       "      <th>mo_sin_old_il_acct</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>dti</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>mths_since_recent_inq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3195</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81024</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223966</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46778</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327073</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95830</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>447972</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>309700</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190536</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360741</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>33619 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        term fico_range_high installment tot_hi_cred_lim mort_acc  \\\n",
       "3195       0               1           2               0        0   \n",
       "81024      0               0           3               4        3   \n",
       "223966     0               2           4               3        1   \n",
       "46778      1               1           1               0        0   \n",
       "327073     1               1           2               4        3   \n",
       "...      ...             ...         ...             ...      ...   \n",
       "95830      0               2           2               3        0   \n",
       "447972     0               2           4               1        0   \n",
       "309700     1               2           1               3        3   \n",
       "190536     0               0           3               1        0   \n",
       "360741     1               2           1               2        0   \n",
       "\n",
       "       bc_open_to_buy  home_ownership  verification_status open_rv_24m  \\\n",
       "3195                3               1                    0           3   \n",
       "81024               0               0                    1           0   \n",
       "223966              4               0                    2           4   \n",
       "46778               0               2                    0           1   \n",
       "327073              3               0                    0           3   \n",
       "...               ...             ...                  ...         ...   \n",
       "95830               4               2                    0           1   \n",
       "447972              3               1                    0           3   \n",
       "309700              3               1                    1           0   \n",
       "190536              3               1                    1           3   \n",
       "360741              3               0                    0           2   \n",
       "\n",
       "       bc_util num_actv_rev_tl mo_sin_old_rev_tl_op  emp_length num_il_tl  \\\n",
       "3195         1               3                    4           2         0   \n",
       "81024        5               0                    1           4         1   \n",
       "223966       0               0                    2           4         1   \n",
       "46778        5               0                    4           4         0   \n",
       "327073       3               4                    2           4         4   \n",
       "...        ...             ...                  ...         ...       ...   \n",
       "95830        0               0                    4           2         3   \n",
       "447972       1               0                    0           2         4   \n",
       "309700       1               0                    4           4         4   \n",
       "190536       1               3                    1           2         1   \n",
       "360741       1               2                    1           4         4   \n",
       "\n",
       "       mo_sin_old_il_acct annual_inc dti  inq_last_6mths mths_since_recent_inq  \n",
       "3195                    1          0   3               1                     0  \n",
       "81024                   3          4   1               0                     4  \n",
       "223966                  2          1   2               0                     1  \n",
       "46778                   5          2   1               0                     4  \n",
       "327073                  5          2   4               0                     3  \n",
       "...                   ...        ...  ..             ...                   ...  \n",
       "95830                   5          4   2               0                     3  \n",
       "447972                  2          0   5               0                     2  \n",
       "309700                  5          2   2               0                     4  \n",
       "190536                  2          1   3               1                     1  \n",
       "360741                  5          1   2               0                     3  \n",
       "\n",
       "[33619 rows x 19 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample1_bins_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "a44eba2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([sample1_bins_1,data_y_1],axis = 1).to_excel('最终2019年-1改变分箱最后19个特征数据的分箱.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "d3725edb",
   "metadata": {},
   "outputs": [],
   "source": [
    "dis_cols = ['term','home_ownership','verification_status','emp_length']#,,'bc_open_to_buy''bc_open_to_buy','bc_util','mo_sin_old_il_acct','dti','mths_since_recent_inq'#]\n",
    "sample1_bins = copy.deepcopy(data_X_2)\n",
    "for k,v in 分箱.items():\n",
    "    x = sample1_bins[k]\n",
    "    bins = 分箱.get(k)\n",
    "    if k in dis_cols:\n",
    "        old_values,new_values = [],[]\n",
    "        for k_,v_ in bins.items():\n",
    "            old_values.extend(v_)\n",
    "            new_values.extend([k_] * len(v_))\n",
    "            sample1_bins[k] = x.replace(old_values + [np.nan],new_values + [空值分箱.get(k)])\n",
    "    else:\n",
    "        labels = list(np.arange(0,len(bins) - 1,dtype = int))\n",
    "        x = pd.cut(x,bins = bins,labels = labels)\n",
    "        sample1_bins[k] = x.replace([np.nan],[空值分箱.get(k)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "4575ea13",
   "metadata": {},
   "outputs": [],
   "source": [
    "sample1_bins_2=sample1_bins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "4fb1974f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>term</th>\n",
       "      <th>fico_range_high</th>\n",
       "      <th>installment</th>\n",
       "      <th>tot_hi_cred_lim</th>\n",
       "      <th>mort_acc</th>\n",
       "      <th>bc_open_to_buy</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>open_rv_24m</th>\n",
       "      <th>bc_util</th>\n",
       "      <th>num_actv_rev_tl</th>\n",
       "      <th>mo_sin_old_rev_tl_op</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>num_il_tl</th>\n",
       "      <th>mo_sin_old_il_acct</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>dti</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>mths_since_recent_inq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>169021</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204979</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>501420</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8855</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>264696</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>405068</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144704</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61945</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506323</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79190</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>33595 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        term fico_range_high installment tot_hi_cred_lim mort_acc  \\\n",
       "169021     1               3           3               3        0   \n",
       "204979     1               0           3               3        3   \n",
       "501420     0               0           2               1        0   \n",
       "8855       0               2           1               2        0   \n",
       "264696     0               0           2               2        0   \n",
       "...      ...             ...         ...             ...      ...   \n",
       "405068     0               2           3               3        0   \n",
       "144704     0               3           1               1        0   \n",
       "61945      1               0           3               3        1   \n",
       "506323     0               1           1               2        3   \n",
       "79190      0               0           1               1        0   \n",
       "\n",
       "       bc_open_to_buy  home_ownership  verification_status open_rv_24m  \\\n",
       "169021              0               1                    1           0   \n",
       "204979              1               0                    0           0   \n",
       "501420              3               1                    0           4   \n",
       "8855                4               0                    0           3   \n",
       "264696              1               2                    1           0   \n",
       "...               ...             ...                  ...         ...   \n",
       "405068              1               1                    2           1   \n",
       "144704              4               0                    0           0   \n",
       "61945               0               0                    1           3   \n",
       "506323              3               0                    0           0   \n",
       "79190               5               2                    1           0   \n",
       "\n",
       "       bc_util num_actv_rev_tl mo_sin_old_rev_tl_op  emp_length num_il_tl  \\\n",
       "169021       4               0                    4           3         3   \n",
       "204979       3               1                    2           2         4   \n",
       "501420       1               4                    3           4         3   \n",
       "8855         3               3                    4           0         4   \n",
       "264696       1               2                    2           4         4   \n",
       "...        ...             ...                  ...         ...       ...   \n",
       "405068       1               2                    3           4         4   \n",
       "144704       0               0                    2           2         2   \n",
       "61945        5               4                    1           2         3   \n",
       "506323       0               0                    0           0         1   \n",
       "79190        0               1                    4           1         3   \n",
       "\n",
       "       mo_sin_old_il_acct annual_inc dti  inq_last_6mths mths_since_recent_inq  \n",
       "169021                  5          2   2               0                     4  \n",
       "204979                  4          1   5               1                     0  \n",
       "501420                  5          2   2               2                     0  \n",
       "8855                    5          2   4               1                     1  \n",
       "264696                  5          2   2               0                     3  \n",
       "...                   ...        ...  ..             ...                   ...  \n",
       "405068                  5          2   2               0                     4  \n",
       "144704                  4          2   1               0                     1  \n",
       "61945                   3          4   3               0                     3  \n",
       "506323                  5          1   1               0                     2  \n",
       "79190                   4          1   2               1                     0  \n",
       "\n",
       "[33595 rows x 19 columns]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample1_bins_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "06833a10",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([sample1_bins_2,data_y_2],axis = 1).to_excel('最终2019年-2改变分箱最后19个特征数据的分箱.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "604d0915",
   "metadata": {},
   "outputs": [],
   "source": [
    "dis_cols = ['term','home_ownership','verification_status','emp_length']#,,'bc_open_to_buy''bc_open_to_buy','bc_util','mo_sin_old_il_acct','dti','mths_since_recent_inq'#]\n",
    "sample1_bins = copy.deepcopy(data_X_3)\n",
    "for k,v in 分箱.items():\n",
    "    x = sample1_bins[k]\n",
    "    bins = 分箱.get(k)\n",
    "    if k in dis_cols:\n",
    "        old_values,new_values = [],[]\n",
    "        for k_,v_ in bins.items():\n",
    "            old_values.extend(v_)\n",
    "            new_values.extend([k_] * len(v_))\n",
    "            sample1_bins[k] = x.replace(old_values + [np.nan],new_values + [空值分箱.get(k)])\n",
    "    else:\n",
    "        labels = list(np.arange(0,len(bins) - 1,dtype = int))\n",
    "        x = pd.cut(x,bins = bins,labels = labels)\n",
    "        sample1_bins[k] = x.replace([np.nan],[空值分箱.get(k)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "0a486980",
   "metadata": {},
   "outputs": [],
   "source": [
    "sample1_bins_3=sample1_bins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "57c9daf9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>term</th>\n",
       "      <th>fico_range_high</th>\n",
       "      <th>installment</th>\n",
       "      <th>tot_hi_cred_lim</th>\n",
       "      <th>mort_acc</th>\n",
       "      <th>bc_open_to_buy</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>open_rv_24m</th>\n",
       "      <th>bc_util</th>\n",
       "      <th>num_actv_rev_tl</th>\n",
       "      <th>mo_sin_old_rev_tl_op</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>num_il_tl</th>\n",
       "      <th>mo_sin_old_il_acct</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>dti</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>mths_since_recent_inq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39931</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>310418</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250156</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>400224</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56466</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54084</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>351577</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232910</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>457455</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17505</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>33792 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        term fico_range_high installment tot_hi_cred_lim mort_acc  \\\n",
       "39931      1               0           2               1        0   \n",
       "310418     1               2           2               3        1   \n",
       "250156     0               0           4               1        0   \n",
       "400224     1               1           1               0        0   \n",
       "56466      0               0           2               2        0   \n",
       "...      ...             ...         ...             ...      ...   \n",
       "54084      1               0           2               2        1   \n",
       "351577     0               2           3               2        1   \n",
       "232910     0               3           4               3        2   \n",
       "457455     0               0           0               0        0   \n",
       "17505      0               0           1               1        0   \n",
       "\n",
       "       bc_open_to_buy  home_ownership  verification_status open_rv_24m  \\\n",
       "39931               0               2                    1           0   \n",
       "310418              4               0                    2           1   \n",
       "250156              0               2                    2           4   \n",
       "400224              1               2                    0           3   \n",
       "56466               0               0                    1           0   \n",
       "...               ...             ...                  ...         ...   \n",
       "54084               3               1                    2           3   \n",
       "351577              4               1                    0           0   \n",
       "232910              5               0                    2           3   \n",
       "457455              1               2                    0           0   \n",
       "17505               0               1                    0           0   \n",
       "\n",
       "       bc_util num_actv_rev_tl mo_sin_old_rev_tl_op  emp_length num_il_tl  \\\n",
       "39931        5               2                    4           4         4   \n",
       "310418       3               0                    4           4         3   \n",
       "250156       1               2                    1           2         1   \n",
       "400224       3               2                    0           0         0   \n",
       "56466        3               0                    2           2         3   \n",
       "...        ...             ...                  ...         ...       ...   \n",
       "54084        4               4                    4           4         0   \n",
       "351577       1               0                    4           3         3   \n",
       "232910       0               1                    4           0         3   \n",
       "457455       0               1                    2           2         2   \n",
       "17505        5               0                    4           3         2   \n",
       "\n",
       "       mo_sin_old_il_acct annual_inc dti  inq_last_6mths mths_since_recent_inq  \n",
       "39931                   5          1   3               0                     4  \n",
       "310418                  5          2   2               1                     0  \n",
       "250156                  2          2   1               3                     0  \n",
       "400224                  1          1   2               0                     4  \n",
       "56466                   4          2   2               1                     1  \n",
       "...                   ...        ...  ..             ...                   ...  \n",
       "54084                   5          2   4               1                     0  \n",
       "351577                  5          4   1               0                     4  \n",
       "232910                  5          0   5               0                     4  \n",
       "457455                  5          2   1               0                     3  \n",
       "17505                   5          2   1               0                     4  \n",
       "\n",
       "[33792 rows x 19 columns]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample1_bins_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "8dfc0d01",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([sample1_bins_3,data_y_3],axis = 1).to_excel('最终2019年-3改变分箱最后19个特征数据的分箱.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "273b5219",
   "metadata": {},
   "outputs": [],
   "source": [
    "dis_cols = ['term','home_ownership','verification_status','emp_length']#,,'bc_open_to_buy''bc_open_to_buy','bc_util','mo_sin_old_il_acct','dti','mths_since_recent_inq'#]\n",
    "sample1_bins = copy.deepcopy(data_X_4)\n",
    "for k,v in 分箱.items():\n",
    "    x = sample1_bins[k]\n",
    "    bins = 分箱.get(k)\n",
    "    if k in dis_cols:\n",
    "        old_values,new_values = [],[]\n",
    "        for k_,v_ in bins.items():\n",
    "            old_values.extend(v_)\n",
    "            new_values.extend([k_] * len(v_))\n",
    "            sample1_bins[k] = x.replace(old_values + [np.nan],new_values + [空值分箱.get(k)])\n",
    "    else:\n",
    "        labels = list(np.arange(0,len(bins) - 1,dtype = int))\n",
    "        x = pd.cut(x,bins = bins,labels = labels)\n",
    "        sample1_bins[k] = x.replace([np.nan],[空值分箱.get(k)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "e60f3758",
   "metadata": {},
   "outputs": [],
   "source": [
    "sample1_bins_4=sample1_bins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "65e35ce6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>term</th>\n",
       "      <th>fico_range_high</th>\n",
       "      <th>installment</th>\n",
       "      <th>tot_hi_cred_lim</th>\n",
       "      <th>mort_acc</th>\n",
       "      <th>bc_open_to_buy</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>open_rv_24m</th>\n",
       "      <th>bc_util</th>\n",
       "      <th>num_actv_rev_tl</th>\n",
       "      <th>mo_sin_old_rev_tl_op</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>num_il_tl</th>\n",
       "      <th>mo_sin_old_il_acct</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>dti</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>mths_since_recent_inq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>34884</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218524</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>444549</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246062</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>281419</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173517</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17472</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80766</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454729</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21243</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>33541 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        term fico_range_high installment tot_hi_cred_lim mort_acc  \\\n",
       "34884      1               0           3               2        0   \n",
       "218524     0               2           4               2        0   \n",
       "444549     0               2           2               3        3   \n",
       "246062     1               3           3               1        0   \n",
       "281419     0               2           4               4        3   \n",
       "...      ...             ...         ...             ...      ...   \n",
       "173517     0               1           4               4        1   \n",
       "17472      0               2           1               0        0   \n",
       "80766      0               0           1               1        0   \n",
       "454729     1               1           3               1        0   \n",
       "21243      0               0           1               4        1   \n",
       "\n",
       "       bc_open_to_buy  home_ownership  verification_status open_rv_24m  \\\n",
       "34884               3               2                    1           3   \n",
       "218524              5               2                    1           0   \n",
       "444549              4               0                    0           0   \n",
       "246062              4               2                    1           0   \n",
       "281419              5               0                    1           3   \n",
       "...               ...             ...                  ...         ...   \n",
       "173517              4               0                    0           0   \n",
       "17472               4               2                    0           0   \n",
       "80766               0               2                    1           2   \n",
       "454729              3               1                    1           0   \n",
       "21243               1               0                    2           2   \n",
       "\n",
       "       bc_util num_actv_rev_tl mo_sin_old_rev_tl_op  emp_length num_il_tl  \\\n",
       "34884        1               2                    3           2         4   \n",
       "218524       1               1                    4           2         3   \n",
       "444549       1               2                    3           2         4   \n",
       "246062       1               0                    4           1         1   \n",
       "281419       1               3                    3           4         3   \n",
       "...        ...             ...                  ...         ...       ...   \n",
       "173517       1               0                    1           3         0   \n",
       "17472        0               0                    2           4         2   \n",
       "80766        4               0                    2           1         4   \n",
       "454729       3               0                    3           1         2   \n",
       "21243        4               0                    3           3         2   \n",
       "\n",
       "       mo_sin_old_il_acct annual_inc dti  inq_last_6mths mths_since_recent_inq  \n",
       "34884                   5          3   2               2                     0  \n",
       "218524                  5          4   3               0                     4  \n",
       "444549                  5          2   3               0                     0  \n",
       "246062                  5          2   2               0                     4  \n",
       "281419                  5          4   2               0                     4  \n",
       "...                   ...        ...  ..             ...                   ...  \n",
       "173517                  1          4   1               0                     1  \n",
       "17472                   5          3   1               1                     1  \n",
       "80766                   5          1   4               0                     4  \n",
       "454729                  2          2   2               1                     1  \n",
       "21243                   4          4   1               2                     1  \n",
       "\n",
       "[33541 rows x 19 columns]"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample1_bins_4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "907b839c",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([sample1_bins_4,data_y_4],axis = 1).to_excel('最终2019年-4改变分箱最后19个特征数据的分箱.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36eb989b",
   "metadata": {},
   "source": [
    "# 分箱"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "133adb4a",
   "metadata": {},
   "source": [
    "## 区分连续和离散特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "b98d3719",
   "metadata": {},
   "outputs": [],
   "source": [
    "continuous = ['loan_amnt','funded_amnt','funded_amnt_inv','int_rate','installment','annual_inc','dti','delinq_2yrs',\n",
    "           'fico_range_low','fico_range_high','inq_last_6mths','mths_since_last_delinq','mths_since_last_record',\n",
    "           'open_acc','pub_rec','revol_bal','revol_util','total_acc','out_prncp','out_prncp_inv','total_pymnt','total_pymnt_inv',\n",
    "           'total_rec_prncp','total_rec_int','total_rec_late_fee','recoveries','collection_recovery_fee',\n",
    "           'last_pymnt_amnt','last_fico_range_high','last_fico_range_low','collections_12_mths_ex_med','mths_since_last_major_derog',\n",
    "           'policy_code','annual_inc_joint','dti_joint','acc_now_delinq','tot_coll_amt','tot_cur_bal',\n",
    "           'open_acc_6m','open_act_il','open_il_12m','open_il_24m','mths_since_rcnt_il','total_bal_il','il_util',\n",
    "           'open_rv_12m','open_rv_24m','max_bal_bc','all_util','total_rev_hi_lim','inq_fi','total_cu_tl','inq_last_12m',\n",
    "           'acc_open_past_24mths','avg_cur_bal','bc_open_to_buy','bc_util','chargeoff_within_12_mths','delinq_amnt',\n",
    "           'mo_sin_old_il_acct','mo_sin_old_rev_tl_op','mo_sin_rcnt_rev_tl_op','mo_sin_rcnt_tl','mort_acc','mths_since_recent_bc',\n",
    "           'mths_since_recent_bc_dlq','mths_since_recent_inq','mths_since_recent_revol_delinq','num_accts_ever_120_pd','num_actv_bc_tl',\n",
    "           'num_actv_rev_tl','num_bc_sats','num_bc_tl','num_il_tl','num_op_rev_tl','num_rev_accts','num_rev_tl_bal_gt_0','num_sats',\n",
    "           'num_tl_120dpd_2m','num_tl_30dpd','num_tl_90g_dpd_24m','num_tl_op_past_12m','pct_tl_nvr_dlq','percent_bc_gt_75',\n",
    "           'pub_rec_bankruptcies','tax_liens','tot_hi_cred_lim','total_bal_ex_mort','total_bc_limit','total_il_high_credit_limit',\n",
    "           'revol_bal_joint','sec_app_fico_range_low','sec_app_fico_range_high','sec_app_inq_last_6mths','sec_app_mort_acc',\n",
    "           'sec_app_open_acc','sec_app_revol_util','sec_app_open_act_il','sec_app_num_rev_accts','sec_app_chargeoff_within_12_mths',\n",
    "           'sec_app_collections_12_mths_ex_med','sec_app_mths_since_last_major_derog','hardship_length','hardship_dpd',\n",
    "           'orig_projected_additional_accrued_interest','hardship_payoff_balance_amount','hardship_last_payment_amount',\n",
    "           'settlement_amount','settlement_percentage','settlement_term']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "3db99848",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "连续特征 有15个\n",
      "['fico_range_high', 'installment', 'tot_hi_cred_lim', 'mort_acc', 'bc_open_to_buy', 'open_rv_24m', 'bc_util', 'num_actv_rev_tl', 'mo_sin_old_rev_tl_op', 'num_il_tl', 'mo_sin_old_il_acct', 'annual_inc', 'dti', 'inq_last_6mths', 'mths_since_recent_inq']\n",
      "\n",
      "离散特征 有4个\n",
      "['term', 'home_ownership', 'verification_status', 'emp_length']\n"
     ]
    }
   ],
   "source": [
    "continuous_var = [x for x in data_X.columns if x in continuous]\n",
    "categorical_var = [x for x in data_X.columns if x not in continuous]\n",
    "print('连续特征','有{}个'.format(len(continuous_var)))\n",
    "print(continuous_var)\n",
    "print()\n",
    "print('离散特征','有{}个'.format(len(categorical_var)))\n",
    "print(categorical_var)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "36eb876b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>term</th>\n",
       "      <th>fico_range_high</th>\n",
       "      <th>installment</th>\n",
       "      <th>tot_hi_cred_lim</th>\n",
       "      <th>mort_acc</th>\n",
       "      <th>bc_open_to_buy</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>open_rv_24m</th>\n",
       "      <th>bc_util</th>\n",
       "      <th>num_actv_rev_tl</th>\n",
       "      <th>mo_sin_old_rev_tl_op</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>num_il_tl</th>\n",
       "      <th>mo_sin_old_il_acct</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>dti</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>mths_since_recent_inq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>36 months</td>\n",
       "      <td>689</td>\n",
       "      <td>206.13</td>\n",
       "      <td>119576</td>\n",
       "      <td>1</td>\n",
       "      <td>3731.0</td>\n",
       "      <td>OWN</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>0</td>\n",
       "      <td>34.4</td>\n",
       "      <td>2</td>\n",
       "      <td>36</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>7</td>\n",
       "      <td>132.0</td>\n",
       "      <td>85000.0</td>\n",
       "      <td>22.62</td>\n",
       "      <td>1</td>\n",
       "      <td>2.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>36 months</td>\n",
       "      <td>664</td>\n",
       "      <td>542.07</td>\n",
       "      <td>336009</td>\n",
       "      <td>1</td>\n",
       "      <td>12318.0</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>Verified</td>\n",
       "      <td>3</td>\n",
       "      <td>67.4</td>\n",
       "      <td>6</td>\n",
       "      <td>157</td>\n",
       "      <td>5 years</td>\n",
       "      <td>7</td>\n",
       "      <td>164.0</td>\n",
       "      <td>58240.0</td>\n",
       "      <td>39.53</td>\n",
       "      <td>0</td>\n",
       "      <td>8.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>36 months</td>\n",
       "      <td>769</td>\n",
       "      <td>31.14</td>\n",
       "      <td>43225</td>\n",
       "      <td>0</td>\n",
       "      <td>33504.0</td>\n",
       "      <td>RENT</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5</td>\n",
       "      <td>160</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>108.0</td>\n",
       "      <td>26000.0</td>\n",
       "      <td>15.73</td>\n",
       "      <td>1</td>\n",
       "      <td>3.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>36 months</td>\n",
       "      <td>669</td>\n",
       "      <td>108.14</td>\n",
       "      <td>26381</td>\n",
       "      <td>3</td>\n",
       "      <td>60.0</td>\n",
       "      <td>RENT</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>4</td>\n",
       "      <td>98.6</td>\n",
       "      <td>4</td>\n",
       "      <td>149</td>\n",
       "      <td>3 years</td>\n",
       "      <td>7</td>\n",
       "      <td>165.0</td>\n",
       "      <td>30000.0</td>\n",
       "      <td>12.88</td>\n",
       "      <td>0</td>\n",
       "      <td>8.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>36 months</td>\n",
       "      <td>684</td>\n",
       "      <td>70.71</td>\n",
       "      <td>140588</td>\n",
       "      <td>0</td>\n",
       "      <td>13358.0</td>\n",
       "      <td>RENT</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>5</td>\n",
       "      <td>44.9</td>\n",
       "      <td>5</td>\n",
       "      <td>43</td>\n",
       "      <td>3 years</td>\n",
       "      <td>36</td>\n",
       "      <td>115.0</td>\n",
       "      <td>53000.0</td>\n",
       "      <td>21.86</td>\n",
       "      <td>1</td>\n",
       "      <td>6.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518090</th>\n",
       "      <td>60 months</td>\n",
       "      <td>704</td>\n",
       "      <td>1072.04</td>\n",
       "      <td>528499</td>\n",
       "      <td>3</td>\n",
       "      <td>158.0</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>Verified</td>\n",
       "      <td>0</td>\n",
       "      <td>100.3</td>\n",
       "      <td>4</td>\n",
       "      <td>235</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>12</td>\n",
       "      <td>148.0</td>\n",
       "      <td>61908.0</td>\n",
       "      <td>54.03</td>\n",
       "      <td>1</td>\n",
       "      <td>4.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518099</th>\n",
       "      <td>60 months</td>\n",
       "      <td>669</td>\n",
       "      <td>382.77</td>\n",
       "      <td>62127</td>\n",
       "      <td>2</td>\n",
       "      <td>6804.0</td>\n",
       "      <td>OWN</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>12</td>\n",
       "      <td>49.1</td>\n",
       "      <td>9</td>\n",
       "      <td>258</td>\n",
       "      <td>3 years</td>\n",
       "      <td>13</td>\n",
       "      <td>159.0</td>\n",
       "      <td>125370.0</td>\n",
       "      <td>12.99</td>\n",
       "      <td>1</td>\n",
       "      <td>5.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518100</th>\n",
       "      <td>36 months</td>\n",
       "      <td>689</td>\n",
       "      <td>295.39</td>\n",
       "      <td>299258</td>\n",
       "      <td>2</td>\n",
       "      <td>9690.0</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>2</td>\n",
       "      <td>60.6</td>\n",
       "      <td>6</td>\n",
       "      <td>353</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>3</td>\n",
       "      <td>173.0</td>\n",
       "      <td>65000.0</td>\n",
       "      <td>15.90</td>\n",
       "      <td>0</td>\n",
       "      <td>1.200000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518102</th>\n",
       "      <td>36 months</td>\n",
       "      <td>709</td>\n",
       "      <td>108.07</td>\n",
       "      <td>63484</td>\n",
       "      <td>0</td>\n",
       "      <td>2489.0</td>\n",
       "      <td>OWN</td>\n",
       "      <td>Not Verified</td>\n",
       "      <td>5</td>\n",
       "      <td>49.2</td>\n",
       "      <td>5</td>\n",
       "      <td>125</td>\n",
       "      <td>10+ years</td>\n",
       "      <td>5</td>\n",
       "      <td>37.0</td>\n",
       "      <td>44000.0</td>\n",
       "      <td>31.01</td>\n",
       "      <td>1</td>\n",
       "      <td>3.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518106</th>\n",
       "      <td>36 months</td>\n",
       "      <td>664</td>\n",
       "      <td>529.97</td>\n",
       "      <td>180600</td>\n",
       "      <td>3</td>\n",
       "      <td>10247.0</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>Source Verified</td>\n",
       "      <td>3</td>\n",
       "      <td>78.9</td>\n",
       "      <td>11</td>\n",
       "      <td>230</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>148.0</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>9.76</td>\n",
       "      <td>0</td>\n",
       "      <td>1.000000e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>134548 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              term  fico_range_high  installment  tot_hi_cred_lim  mort_acc  \\\n",
       "1        36 months              689       206.13           119576         1   \n",
       "5        36 months              664       542.07           336009         1   \n",
       "11       36 months              769        31.14            43225         0   \n",
       "14       36 months              669       108.14            26381         3   \n",
       "15       36 months              684        70.71           140588         0   \n",
       "...            ...              ...          ...              ...       ...   \n",
       "518090   60 months              704      1072.04           528499         3   \n",
       "518099   60 months              669       382.77            62127         2   \n",
       "518100   36 months              689       295.39           299258         2   \n",
       "518102   36 months              709       108.07            63484         0   \n",
       "518106   36 months              664       529.97           180600         3   \n",
       "\n",
       "        bc_open_to_buy home_ownership verification_status  open_rv_24m  \\\n",
       "1               3731.0            OWN     Source Verified            0   \n",
       "5              12318.0       MORTGAGE            Verified            3   \n",
       "11             33504.0           RENT        Not Verified            2   \n",
       "14                60.0           RENT     Source Verified            4   \n",
       "15             13358.0           RENT     Source Verified            5   \n",
       "...                ...            ...                 ...          ...   \n",
       "518090           158.0       MORTGAGE            Verified            0   \n",
       "518099          6804.0            OWN        Not Verified           12   \n",
       "518100          9690.0       MORTGAGE     Source Verified            2   \n",
       "518102          2489.0            OWN        Not Verified            5   \n",
       "518106         10247.0       MORTGAGE     Source Verified            3   \n",
       "\n",
       "        bc_util  num_actv_rev_tl  mo_sin_old_rev_tl_op emp_length  num_il_tl  \\\n",
       "1          34.4                2                    36  10+ years          7   \n",
       "5          67.4                6                   157    5 years          7   \n",
       "11          3.0                5                   160        NaN          6   \n",
       "14         98.6                4                   149    3 years          7   \n",
       "15         44.9                5                    43    3 years         36   \n",
       "...         ...              ...                   ...        ...        ...   \n",
       "518090    100.3                4                   235  10+ years         12   \n",
       "518099     49.1                9                   258    3 years         13   \n",
       "518100     60.6                6                   353  10+ years          3   \n",
       "518102     49.2                5                   125  10+ years          5   \n",
       "518106     78.9               11                   230        NaN          2   \n",
       "\n",
       "        mo_sin_old_il_acct  annual_inc    dti  inq_last_6mths  \\\n",
       "1                    132.0     85000.0  22.62               1   \n",
       "5                    164.0     58240.0  39.53               0   \n",
       "11                   108.0     26000.0  15.73               1   \n",
       "14                   165.0     30000.0  12.88               0   \n",
       "15                   115.0     53000.0  21.86               1   \n",
       "...                    ...         ...    ...             ...   \n",
       "518090               148.0     61908.0  54.03               1   \n",
       "518099               159.0    125370.0  12.99               1   \n",
       "518100               173.0     65000.0  15.90               0   \n",
       "518102                37.0     44000.0  31.01               1   \n",
       "518106               148.0    150000.0   9.76               0   \n",
       "\n",
       "        mths_since_recent_inq  \n",
       "1                2.000000e+00  \n",
       "5                8.000000e+00  \n",
       "11               3.000000e+00  \n",
       "14               8.000000e+00  \n",
       "15               6.000000e+00  \n",
       "...                       ...  \n",
       "518090           4.000000e+00  \n",
       "518099           5.000000e+00  \n",
       "518100           1.200000e+01  \n",
       "518102           3.000000e+00  \n",
       "518106           1.000000e+09  \n",
       "\n",
       "[134548 rows x 19 columns]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_X"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c96e789",
   "metadata": {},
   "source": [
    "## 分箱映射"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "a06b25a5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|█████████████████████████████████████████████████████████████████████████████████| 58/58 [00:00<00:00, 123.38it/s]\n"
     ]
    }
   ],
   "source": [
    "continuous_bin = pd.DataFrame()\n",
    "for i in tqdm.tqdm(continuous_var_bin_dict.keys()):\n",
    "    try:\n",
    "        continuous_bin = pd.concat([continuous_bin,vbm.cont_var_bin_map(data_X[i],continuous_var_bin_dict.get(i))],axis = 1)\n",
    "    except:\n",
    "        continue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "ce275a7d",
   "metadata": {},
   "outputs": [],
   "source": [
    "continuous_cols = [x[:-4] for x in continuous_bin.columns]\n",
    "continuous_bin = continuous_bin.rename(columns = dict(zip(continuous_bin.columns,continuous_cols)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "17e77f44",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|████████████████████████████████████████████████████████████████████████████████████| 9/9 [00:00<00:00, 48.38it/s]\n"
     ]
    }
   ],
   "source": [
    "categorical_bin = pd.DataFrame()\n",
    "for i in tqdm.tqdm(categorical_var_bin_dict.keys()):\n",
    "    try:\n",
    "        categorical_bin = pd.concat([categorical_bin,vbm.disc_var_bin_map(data_X[i],categorical_var_bin_dict.get(i))],axis = 1)\n",
    "    except:\n",
    "        continue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "57b59833",
   "metadata": {},
   "outputs": [],
   "source": [
    "categorical_cols = [x[:-4] for x in categorical_bin.columns]\n",
    "categorical_bin = categorical_bin.rename(columns = dict(zip(categorical_bin.columns,categorical_cols)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "64c70cc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_X1 = pd.concat([continuous_bin,categorical_bin],axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "6eb30fd8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>installment</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>dti</th>\n",
       "      <th>fico_range_high</th>\n",
       "      <th>open_rv_24m</th>\n",
       "      <th>bc_open_to_buy</th>\n",
       "      <th>bc_util</th>\n",
       "      <th>mo_sin_old_il_acct</th>\n",
       "      <th>mo_sin_old_rev_tl_op</th>\n",
       "      <th>mort_acc</th>\n",
       "      <th>mths_since_recent_inq</th>\n",
       "      <th>num_actv_rev_tl</th>\n",
       "      <th>num_il_tl</th>\n",
       "      <th>tot_hi_cred_lim</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518090</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518099</th>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518100</th>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518102</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518106</th>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>134548 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        installment  annual_inc  dti  fico_range_high  open_rv_24m  \\\n",
       "1               2.0         3.0  3.0                2            1   \n",
       "5               4.0         3.0  5.0                1            3   \n",
       "11              1.0         1.0  2.0                4            2   \n",
       "14              1.0         1.0  2.0                1            4   \n",
       "15              1.0         3.0  3.0                1            4   \n",
       "...             ...         ...  ...              ...          ...   \n",
       "518090          5.0         3.0  5.0                2            1   \n",
       "518099          3.0         5.0  2.0                1            5   \n",
       "518100          2.0         3.0  2.0                2            2   \n",
       "518102          1.0         2.0  4.0                2            4   \n",
       "518106          4.0         5.0  2.0                1            3   \n",
       "\n",
       "        bc_open_to_buy  bc_util  mo_sin_old_il_acct  mo_sin_old_rev_tl_op  \\\n",
       "1                  1.0      2.0                 5.0                     1   \n",
       "5                  3.0      4.0                 5.0                     5   \n",
       "11                 4.0      1.0                 4.0                     5   \n",
       "14                 1.0      5.0                 5.0                     4   \n",
       "15                 3.0      3.0                 4.0                     1   \n",
       "...                ...      ...                 ...                   ...   \n",
       "518090             1.0      5.0                 5.0                     5   \n",
       "518099             2.0      3.0                 5.0                     5   \n",
       "518100             2.0      4.0                 5.0                     5   \n",
       "518102             1.0      3.0                 2.0                     3   \n",
       "518106             2.0      5.0                 5.0                     5   \n",
       "\n",
       "        mort_acc  mths_since_recent_inq  num_actv_rev_tl  num_il_tl  \\\n",
       "1              2                    3.0                1          4   \n",
       "5              2                    5.0                3          4   \n",
       "11             1                    3.0                2          4   \n",
       "14             4                    5.0                2          4   \n",
       "15             1                    4.0                2          5   \n",
       "...          ...                    ...              ...        ...   \n",
       "518090         4                    4.0                2          5   \n",
       "518099         3                    4.0                4          5   \n",
       "518100         3                    5.0                3          3   \n",
       "518102         1                    3.0                2          4   \n",
       "518106         4                    5.0                5          3   \n",
       "\n",
       "        tot_hi_cred_lim  \n",
       "1                     2  \n",
       "5                     4  \n",
       "11                    2  \n",
       "14                    1  \n",
       "15                    3  \n",
       "...                 ...  \n",
       "518090                5  \n",
       "518099                2  \n",
       "518100                4  \n",
       "518102                2  \n",
       "518106                3  \n",
       "\n",
       "[134548 rows x 14 columns]"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "continuous_bin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "971a1fa6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>term</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518090</th>\n",
       "      <td>2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518099</th>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518100</th>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518102</th>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518106</th>\n",
       "      <td>1</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>134548 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        term  emp_length  home_ownership  verification_status  inq_last_6mths\n",
       "1          1         4.0               2                    2             NaN\n",
       "5          1         3.0               1                    3             NaN\n",
       "11         1         6.0               3                    1             NaN\n",
       "14         1         3.0               3                    2             NaN\n",
       "15         1         3.0               3                    2             NaN\n",
       "...      ...         ...             ...                  ...             ...\n",
       "518090     2         4.0               1                    3             NaN\n",
       "518099     2         3.0               2                    1             NaN\n",
       "518100     1         4.0               1                    2             NaN\n",
       "518102     1         4.0               2                    1             NaN\n",
       "518106     1         6.0               1                    2             NaN\n",
       "\n",
       "[134548 rows x 5 columns]"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categorical_bin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "b91cd1a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([continuous_bin,categorical_bin,data_y],axis = 1).to_excel('最终2019年改变分箱最后19个特征数据的分箱.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c257e9b9",
   "metadata": {},
   "source": [
    "## woe编码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "3b759fac",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|█████████████████████████████████████████████████████████████████████████████████| 19/19 [00:00<00:00, 115.83it/s]\n"
     ]
    }
   ],
   "source": [
    "data_X_woe = pd.DataFrame()\n",
    "for col_x in tqdm.tqdm([x + '_BIN' for x in data_X1.columns]):\n",
    "    guize = woe_list_dict.get(col_x)\n",
    "    data_X_woe[col_x[:-4]] = data_X1[col_x[:-4]].replace(list(guize.index),list(guize.values))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "2edf399d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>installment</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>dti</th>\n",
       "      <th>fico_range_high</th>\n",
       "      <th>open_rv_24m</th>\n",
       "      <th>bc_open_to_buy</th>\n",
       "      <th>bc_util</th>\n",
       "      <th>mo_sin_old_il_acct</th>\n",
       "      <th>mo_sin_old_rev_tl_op</th>\n",
       "      <th>mort_acc</th>\n",
       "      <th>mths_since_recent_inq</th>\n",
       "      <th>num_actv_rev_tl</th>\n",
       "      <th>num_il_tl</th>\n",
       "      <th>tot_hi_cred_lim</th>\n",
       "      <th>term</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>verification_status</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-0.1749</td>\n",
       "      <td>-0.0174</td>\n",
       "      <td>-0.0047</td>\n",
       "      <td>0.1174</td>\n",
       "      <td>-0.1855</td>\n",
       "      <td>0.2281</td>\n",
       "      <td>-0.0756</td>\n",
       "      <td>-0.1164</td>\n",
       "      <td>0.4333</td>\n",
       "      <td>-0.0460</td>\n",
       "      <td>0.1084</td>\n",
       "      <td>-0.1927</td>\n",
       "      <td>-0.0560</td>\n",
       "      <td>0.1650</td>\n",
       "      <td>-0.2489</td>\n",
       "      <td>-0.1468</td>\n",
       "      <td>0.0767</td>\n",
       "      <td>0.0433</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.2830</td>\n",
       "      <td>-0.0174</td>\n",
       "      <td>0.3059</td>\n",
       "      <td>0.3094</td>\n",
       "      <td>0.0320</td>\n",
       "      <td>-0.1053</td>\n",
       "      <td>0.1234</td>\n",
       "      <td>-0.1164</td>\n",
       "      <td>-0.1394</td>\n",
       "      <td>-0.0460</td>\n",
       "      <td>-0.1246</td>\n",
       "      <td>0.1123</td>\n",
       "      <td>-0.0560</td>\n",
       "      <td>-0.3062</td>\n",
       "      <td>-0.2489</td>\n",
       "      <td>-0.0048</td>\n",
       "      <td>-0.2348</td>\n",
       "      <td>0.3207</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>-0.6841</td>\n",
       "      <td>0.3266</td>\n",
       "      <td>-0.1368</td>\n",
       "      <td>-0.5256</td>\n",
       "      <td>-0.0555</td>\n",
       "      <td>-0.2762</td>\n",
       "      <td>-0.2614</td>\n",
       "      <td>-0.0564</td>\n",
       "      <td>-0.1394</td>\n",
       "      <td>0.2471</td>\n",
       "      <td>0.1084</td>\n",
       "      <td>-0.0280</td>\n",
       "      <td>-0.0560</td>\n",
       "      <td>0.1650</td>\n",
       "      <td>-0.2489</td>\n",
       "      <td>0.4692</td>\n",
       "      <td>0.2370</td>\n",
       "      <td>-0.2413</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>-0.6841</td>\n",
       "      <td>0.3266</td>\n",
       "      <td>-0.1368</td>\n",
       "      <td>0.3094</td>\n",
       "      <td>0.1783</td>\n",
       "      <td>0.2281</td>\n",
       "      <td>0.2002</td>\n",
       "      <td>-0.1164</td>\n",
       "      <td>-0.0145</td>\n",
       "      <td>-0.4001</td>\n",
       "      <td>-0.1246</td>\n",
       "      <td>-0.0280</td>\n",
       "      <td>-0.0560</td>\n",
       "      <td>0.3561</td>\n",
       "      <td>-0.2489</td>\n",
       "      <td>-0.0048</td>\n",
       "      <td>0.2370</td>\n",
       "      <td>0.0433</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>-0.6841</td>\n",
       "      <td>-0.0174</td>\n",
       "      <td>-0.0047</td>\n",
       "      <td>0.3094</td>\n",
       "      <td>0.1783</td>\n",
       "      <td>-0.1053</td>\n",
       "      <td>0.0429</td>\n",
       "      <td>-0.0564</td>\n",
       "      <td>0.4333</td>\n",
       "      <td>0.2471</td>\n",
       "      <td>-0.0052</td>\n",
       "      <td>-0.0280</td>\n",
       "      <td>-0.1894</td>\n",
       "      <td>-0.1403</td>\n",
       "      <td>-0.2489</td>\n",
       "      <td>-0.0048</td>\n",
       "      <td>0.2370</td>\n",
       "      <td>0.0433</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518090</th>\n",
       "      <td>0.0935</td>\n",
       "      <td>-0.0174</td>\n",
       "      <td>0.3059</td>\n",
       "      <td>0.1174</td>\n",
       "      <td>-0.1855</td>\n",
       "      <td>0.2281</td>\n",
       "      <td>0.2002</td>\n",
       "      <td>-0.1164</td>\n",
       "      <td>-0.1394</td>\n",
       "      <td>-0.4001</td>\n",
       "      <td>-0.0052</td>\n",
       "      <td>-0.0280</td>\n",
       "      <td>-0.1894</td>\n",
       "      <td>-0.5220</td>\n",
       "      <td>0.5847</td>\n",
       "      <td>-0.1468</td>\n",
       "      <td>-0.2348</td>\n",
       "      <td>0.3207</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518099</th>\n",
       "      <td>0.0711</td>\n",
       "      <td>-0.2223</td>\n",
       "      <td>-0.1368</td>\n",
       "      <td>0.3094</td>\n",
       "      <td>0.3573</td>\n",
       "      <td>0.0472</td>\n",
       "      <td>0.0429</td>\n",
       "      <td>-0.1164</td>\n",
       "      <td>-0.1394</td>\n",
       "      <td>-0.2080</td>\n",
       "      <td>-0.0052</td>\n",
       "      <td>0.2552</td>\n",
       "      <td>-0.1894</td>\n",
       "      <td>0.1650</td>\n",
       "      <td>0.5847</td>\n",
       "      <td>-0.0048</td>\n",
       "      <td>0.0767</td>\n",
       "      <td>-0.2413</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518100</th>\n",
       "      <td>-0.1749</td>\n",
       "      <td>-0.0174</td>\n",
       "      <td>-0.1368</td>\n",
       "      <td>0.1174</td>\n",
       "      <td>-0.0555</td>\n",
       "      <td>0.0472</td>\n",
       "      <td>0.1234</td>\n",
       "      <td>-0.1164</td>\n",
       "      <td>-0.1394</td>\n",
       "      <td>-0.2080</td>\n",
       "      <td>-0.1246</td>\n",
       "      <td>0.1123</td>\n",
       "      <td>0.1325</td>\n",
       "      <td>-0.3062</td>\n",
       "      <td>-0.2489</td>\n",
       "      <td>-0.1468</td>\n",
       "      <td>-0.2348</td>\n",
       "      <td>0.0433</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518102</th>\n",
       "      <td>-0.6841</td>\n",
       "      <td>0.1273</td>\n",
       "      <td>0.1502</td>\n",
       "      <td>0.1174</td>\n",
       "      <td>0.1783</td>\n",
       "      <td>0.2281</td>\n",
       "      <td>0.0429</td>\n",
       "      <td>0.1968</td>\n",
       "      <td>0.0649</td>\n",
       "      <td>0.2471</td>\n",
       "      <td>0.1084</td>\n",
       "      <td>-0.0280</td>\n",
       "      <td>-0.0560</td>\n",
       "      <td>0.1650</td>\n",
       "      <td>-0.2489</td>\n",
       "      <td>-0.1468</td>\n",
       "      <td>0.0767</td>\n",
       "      <td>-0.2413</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518106</th>\n",
       "      <td>0.2830</td>\n",
       "      <td>-0.2223</td>\n",
       "      <td>-0.1368</td>\n",
       "      <td>0.3094</td>\n",
       "      <td>0.0320</td>\n",
       "      <td>0.0472</td>\n",
       "      <td>0.2002</td>\n",
       "      <td>-0.1164</td>\n",
       "      <td>-0.1394</td>\n",
       "      <td>-0.4001</td>\n",
       "      <td>-0.1246</td>\n",
       "      <td>0.3719</td>\n",
       "      <td>0.1325</td>\n",
       "      <td>-0.1403</td>\n",
       "      <td>-0.2489</td>\n",
       "      <td>0.4692</td>\n",
       "      <td>-0.2348</td>\n",
       "      <td>0.0433</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>134548 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        installment  annual_inc     dti  fico_range_high  open_rv_24m  \\\n",
       "1           -0.1749     -0.0174 -0.0047           0.1174      -0.1855   \n",
       "5            0.2830     -0.0174  0.3059           0.3094       0.0320   \n",
       "11          -0.6841      0.3266 -0.1368          -0.5256      -0.0555   \n",
       "14          -0.6841      0.3266 -0.1368           0.3094       0.1783   \n",
       "15          -0.6841     -0.0174 -0.0047           0.3094       0.1783   \n",
       "...             ...         ...     ...              ...          ...   \n",
       "518090       0.0935     -0.0174  0.3059           0.1174      -0.1855   \n",
       "518099       0.0711     -0.2223 -0.1368           0.3094       0.3573   \n",
       "518100      -0.1749     -0.0174 -0.1368           0.1174      -0.0555   \n",
       "518102      -0.6841      0.1273  0.1502           0.1174       0.1783   \n",
       "518106       0.2830     -0.2223 -0.1368           0.3094       0.0320   \n",
       "\n",
       "        bc_open_to_buy  bc_util  mo_sin_old_il_acct  mo_sin_old_rev_tl_op  \\\n",
       "1               0.2281  -0.0756             -0.1164                0.4333   \n",
       "5              -0.1053   0.1234             -0.1164               -0.1394   \n",
       "11             -0.2762  -0.2614             -0.0564               -0.1394   \n",
       "14              0.2281   0.2002             -0.1164               -0.0145   \n",
       "15             -0.1053   0.0429             -0.0564                0.4333   \n",
       "...                ...      ...                 ...                   ...   \n",
       "518090          0.2281   0.2002             -0.1164               -0.1394   \n",
       "518099          0.0472   0.0429             -0.1164               -0.1394   \n",
       "518100          0.0472   0.1234             -0.1164               -0.1394   \n",
       "518102          0.2281   0.0429              0.1968                0.0649   \n",
       "518106          0.0472   0.2002             -0.1164               -0.1394   \n",
       "\n",
       "        mort_acc  mths_since_recent_inq  num_actv_rev_tl  num_il_tl  \\\n",
       "1        -0.0460                 0.1084          -0.1927    -0.0560   \n",
       "5        -0.0460                -0.1246           0.1123    -0.0560   \n",
       "11        0.2471                 0.1084          -0.0280    -0.0560   \n",
       "14       -0.4001                -0.1246          -0.0280    -0.0560   \n",
       "15        0.2471                -0.0052          -0.0280    -0.1894   \n",
       "...          ...                    ...              ...        ...   \n",
       "518090   -0.4001                -0.0052          -0.0280    -0.1894   \n",
       "518099   -0.2080                -0.0052           0.2552    -0.1894   \n",
       "518100   -0.2080                -0.1246           0.1123     0.1325   \n",
       "518102    0.2471                 0.1084          -0.0280    -0.0560   \n",
       "518106   -0.4001                -0.1246           0.3719     0.1325   \n",
       "\n",
       "        tot_hi_cred_lim    term  emp_length  home_ownership  \\\n",
       "1                0.1650 -0.2489     -0.1468          0.0767   \n",
       "5               -0.3062 -0.2489     -0.0048         -0.2348   \n",
       "11               0.1650 -0.2489      0.4692          0.2370   \n",
       "14               0.3561 -0.2489     -0.0048          0.2370   \n",
       "15              -0.1403 -0.2489     -0.0048          0.2370   \n",
       "...                 ...     ...         ...             ...   \n",
       "518090          -0.5220  0.5847     -0.1468         -0.2348   \n",
       "518099           0.1650  0.5847     -0.0048          0.0767   \n",
       "518100          -0.3062 -0.2489     -0.1468         -0.2348   \n",
       "518102           0.1650 -0.2489     -0.1468          0.0767   \n",
       "518106          -0.1403 -0.2489      0.4692         -0.2348   \n",
       "\n",
       "        verification_status  inq_last_6mths  \n",
       "1                    0.0433             NaN  \n",
       "5                    0.3207             NaN  \n",
       "11                  -0.2413             NaN  \n",
       "14                   0.0433             NaN  \n",
       "15                   0.0433             NaN  \n",
       "...                     ...             ...  \n",
       "518090               0.3207             NaN  \n",
       "518099              -0.2413             NaN  \n",
       "518100               0.0433             NaN  \n",
       "518102              -0.2413             NaN  \n",
       "518106               0.0433             NaN  \n",
       "\n",
       "[134548 rows x 19 columns]"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_X_woe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "5000c9cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([data_X_woe,data_y],axis = 1).to_excel('最终2019年改变分箱最后19个特征数据的入模测试集.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "d6faef99",
   "metadata": {},
   "outputs": [],
   "source": [
    "#pd.concat([data_X,data_y],axis = 1).to_excel('19新数据入模原始测试集.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1dbb503",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
